/*
 * Amkai Enterprise Solution (AES.) 
 * Copyright 2004-2008 by Amkai(TM), Inc.
 * www.amkai.com
 * 
 * Project: webservice
 * Package: com.amkai.aes.webservice.binary.objecthandler.billing
 * File: LedgerHandler.java
 * Created: 2009.04.20. 13:31:51
 * Author: polaj 
 * 
 */
package com.amkai.aes.webservice.binary.objecthandler.billing;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;

import com.amkai.aes.aescommon.AESSettings;
import com.amkai.aes.aescommon.billing.CustomAccountMaintenanceUI;
import com.amkai.aes.aescommon.billing.ledger.AllocatedAmount;
import com.amkai.aes.aescommon.billing.ledger.AllocationTableRow;
import com.amkai.aes.aescommon.billing.ledger.LedgerUtils;
import com.amkai.aes.aescommon.billing.ledger.ThinEntry;
import com.amkai.aes.aescommon.casecosting.CaseCostingProvider;
import com.amkai.aes.aescommon.casecosting.ChargeCdmLogAssociation;
import com.amkai.aes.aescommon.dictionary.SupplyResourceItemManager;
import com.amkai.aes.aescommon.exceptions.AOException;
import com.amkai.aes.aescommon.factory.RADBeanFactory;
import com.amkai.aes.aescommon.ledger.exceptions.LedgerException;
import com.amkai.aes.aescommon.ledger.managers.CorrectionManager;
import com.amkai.aes.aescommon.ledger.managers.details.FromToManager;
import com.amkai.aes.aescommon.ledger.managers.details.ProcedureUtils;
import com.amkai.aes.aescommon.ledger.utils.AllocationUtils;
import com.amkai.aes.aescommon.ledger.utils.BatchUtils;
import com.amkai.aes.aescommon.ledger.utils.ChargeUtils;
import com.amkai.aes.aescommon.ledger.utils.EntryUtils;
import com.amkai.aes.aescommon.ledger.utils.UnassignedPaymentUtils;
import com.amkai.aes.aescommon.manager.DepartmentManager;
import com.amkai.aes.aescommon.organization.BusinessGroupNotFoundException;
import com.amkai.aes.aescommon.registration.AccountUtils;
import com.amkai.aes.aescommon.registration.PmCaseUtils;
import com.amkai.aes.aescommon.service.binary.job.billing.LedgerJob;
import com.amkai.aes.aescommon.service.result.billing.LedgerResult;
import com.amkai.aes.aescommon.utilities.PatientUtils;
import com.amkai.aes.generatedbeans.aging.Aging;
import com.amkai.aes.generatedbeans.bill.BillCharge;
import com.amkai.aes.generatedbeans.bill.BillStatusKind;
import com.amkai.aes.generatedbeans.bill.InsuranceBill;
import com.amkai.aes.generatedbeans.bill.PatientStatement;
import com.amkai.aes.generatedbeans.bill.configuration.TimeAndMaterialConfiguration;
import com.amkai.aes.generatedbeans.billinginterval.Batch;
import com.amkai.aes.generatedbeans.billinginterval.Expected;
import com.amkai.aes.generatedbeans.billingledger.Allocation;
import com.amkai.aes.generatedbeans.billingledger.Amount;
import com.amkai.aes.generatedbeans.billingledger.BaseAmount;
import com.amkai.aes.generatedbeans.billingledger.Charge;
import com.amkai.aes.generatedbeans.billingledger.Correction;
import com.amkai.aes.generatedbeans.billingledger.Entry;
import com.amkai.aes.generatedbeans.billingledger.Payment;
import com.amkai.aes.generatedbeans.billingledger.TransactionKind;
import com.amkai.aes.generatedbeans.billingledger.TransactionType;
import com.amkai.aes.generatedbeans.billingledger.UnassignedPayment;
import com.amkai.aes.generatedbeans.billingledger.details.FromTo;
import com.amkai.aes.generatedbeans.billingledger.details.FromToKind;
import com.amkai.aes.generatedbeans.casecosting.TimeAndMaterialWorksheetRow;
import com.amkai.aes.generatedbeans.cdmcasehistory.CdmCaseHistory;
import com.amkai.aes.generatedbeans.cdmcasehistory.logdetails.phase.DepartmentLog;
import com.amkai.aes.generatedbeans.cdmcasehistory.logdetails.procedure.ProcedureLog;
import com.amkai.aes.generatedbeans.cdmcasehistory.logdetails.resources.SupplyLog;
import com.amkai.aes.generatedbeans.organization.BusinessGroup;
import com.amkai.aes.generatedbeans.organization.Department;
import com.amkai.aes.generatedbeans.passport.Passport;
import com.amkai.aes.generatedbeans.rcm.RCMItemOption;
import com.amkai.aes.generatedbeans.registration.Account;
import com.amkai.aes.generatedbeans.registration.AccountBgSpecificProperties;
import com.amkai.aes.generatedbeans.registration.Patient;
import com.amkai.aes.generatedbeans.scheduling.AbstractAppointment;
import com.amkai.aes.generatedbeans.uibean.ledgerfilter.GroupByKind;
import com.amkai.aes.generatedbeans.uibean.ledgerfilter.LedgerFilter;
import com.amkai.aes.generatedbeans.uibean.ledgerfilter.ViewKind;
import com.amkai.aes.generatedmodels.billing.transaction.TransactionDomain;
import com.amkai.aes.server.billing.AgingCalculatorManager;
import com.amkai.aes.server.billing.TimeAndMaterialServerUtils;
import com.amkai.aes.server.billing.grouprebill.GroupRebillProvider;
import com.amkai.aes.server.billing.ledger.EntryLoaderImpl;
import com.amkai.aes.server.billing.ledger.LedgerManager;
import com.amkai.aes.server.billing.ledger.LedgerServerUtils;
import com.amkai.aes.server.billing.ledger.ServerLedgerEntryLoadManager;
import com.amkai.aes.server.rcm.NewRCMConductor;
import com.amkai.aes.server.session.ServiceSessionManager;
import com.amkai.aes.webservice.binary.JobObjectHandler;
import com.amkai.common.extensions.hibernate.config.SiteManager;
import com.amkai.common.extensions.hibernate.config.manager.SessionManager;
import com.amkai.extensions.server.JdbcManager;
import com.amkai.extensions.server.services.ServerServiceManager;

public class LedgerHandler implements JobObjectHandler<LedgerJob, Department, LedgerResult>{

	private static final Log log = LogFactory.getLog(LedgerHandler.class);
	
	protected static final String TITLE = "Ledger";
	
	private static Passport getRuntimePassport(SessionManager sm) throws Exception {
		return sm.get(Passport.class, ServiceSessionManager.getConnection().getRuntimePassport().getId());
	}
	
	@Override
	public LedgerResult process(LedgerJob job, Department dep) throws Exception {
		switch (job.getKind()) {
		case GET_PATIENT_LEDGER: return getPatientLedger(job,dep);
		case GET_PATIENT_LEDGER2: return getPatientLedger2(job);
		case GET_BATCH_LEDGER:	return getBatchLedger(job,dep);
		case GET_BATCH_LEDGER2:	return getBatchLedger2(job);
		case GET_BATCH_EXPECTED_INFORMATION:	return getBatchExpectedInformation(job);
		case GET_ENTRY:	return getEntry(job,dep);
		case GET_PATIENT:	return getPatient(job,dep);
		case SAVE_ENTRY:	return saveEntry(job, dep);
		// KKORNEL: AES-10645
		case CHECK_BATCH:	return checkBatch(job.getBatch());
		case INIT_ACCOUNT_MAINTENANCE:	return initAccountMaintenance(job, dep);
		case SAVE_ACCOUNT_MAINTENANCE:	return saveAccountMaintenance(job, dep);
		case INIT_ALLOCATION:	return initAllocation(job);
		case INIT_CHARGE_POSTED:	return initChargePosted(job);
//		case INIT_UNASSIGNED_PAYMENT_ALLOCATION:	return initUnassignesPaymentAllocation(job, dep);
//		case COMMIT_UNASSIGNED_PAYMENT_ALLOCATION:	return saveUnassignedPaymentAllocation(job, dep);
		case SAVE_UNASSIGNED_PAYMENT_ALLOCATION:	return saveUnassignedPaymentAllocation(job);
		case GET_CHARGES_BY_ID:	return getChargesByID(job);
		case INIT_ALLOCATION_TABLE_BY_PATIENT:	return initAllocationTableByPatient(job);
		case INIT_ALLOCATION_TABLE_BY_CHARGES:	return initAllocationTableByCharges(job);
		case INIT_PATIENT_LEDGER:	return initPatientLedger(job);
		case GET_CHARGE_CDM_ASSOCIATION:	return getChargeCdmAssociation(job);
		case CREATE_CORRECTION:	return createCorrection(job);
		case INIT_CORRECTION_SCREEN:	return initCorrectionScreen(job);
		case INIT_CHARGE_CDM_ASSOCIATION_SCREEN:	return initChargeCDMAssociationScreen(job);
		
		case INIT_PAYMENT_EDITOR:	return initPaymentEditor(job);
		case INIT_UNASSIGNED_PAYMENT_EDITOR:	return initUnassignedPaymentEditor(job);
		case DOWNLOAD_PATIENT_FOR_PATIENT_LEDGER:	return downloadPatientForPatientLedger(job);
		case DOWNLOAD_THIN_PATIENT:	return downloadThinPatient(job);
		case CHARGES_ADD_BILL_DATE:	return addBillDate(job);
		case CHARGES_REMOVE_BILL_DATE:	return removeBillDate(job);
		case CHARGES_SET_GC:	return setGenerateClaim(job, true);
		case CHARGES_UNSET_GC:	return setGenerateClaim(job, false);
		}
		throw new AOException(TITLE, "Unknown Ledger Kind", job.getKind().toString(), null);
	}

	private LedgerResult getPatient(LedgerJob job, Department department) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			result.setThinPatient(LedgerServerUtils.getPatient(sm, job.getPatientID(), department));
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getPatientLedger(LedgerJob job, Department dep) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			result.setTransactions(LedgerServerUtils.getPatientLedger(sm, job.getPatientID(), dep));
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getPatientLedger2(LedgerJob job) throws Exception {
		long start = System.currentTimeMillis();
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			result.setThinEntries(LedgerServerUtils.getPatientLedger3(sm, job.getPatientID(), job.getModuleDepartment()));
//			result.setThinEntries(LedgerServerUtils.getPatientLedger(sm, job.getPatientID(), null, job.getModuleDepartment()));
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		log.debug(String.format("Patient ledger query time: %d msec", System.currentTimeMillis()-start));
		return result;
	}
	
	private LedgerResult getEntry(LedgerJob job, Department dep) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			EntryLoaderImpl loader = new EntryLoaderImpl(sm);
			result.setEntry(loader.loadEntry(job.getEntryID()));
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult saveEntry(LedgerJob job, Department dep) throws Exception {

		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			sm.openTransaction();
			Entry entry = job.getEntry();
			entry.putTranzientProperty(LedgerManager.NEW_ACTIVE_STATUS_KEY, job.getNewActiveStatus());
			LedgerManager.saveEntry(sm, entry);
			result.setEntry(entry);
			sm.commitTransaction();
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getBatchLedger(LedgerJob job, Department dep) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			List<Entry> l = sm.execute("SELECT e FROM Entry e WHERE e.batch=:batch").setParameter("batch", job.getBatch()).list();
			List<Entry> transactions = new ArrayList<Entry>();
			for (Entry entry : l) {
				transactions.add(ServerLedgerEntryLoadManager.loadEntry(entry, sm, false, dep));
			}
			result.setBatchLedger(transactions);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getBatchLedger2(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			Connection conn = JdbcManager.createJDBCConnectionFactory().getJDBCConnection();
			PreparedStatement ps = conn.prepareStatement("SELECT * FROM v_batch_ledger WHERE batch_id=?");
			ps.setLong(1, job.getBatch().getId());
			ResultSet rs = ps.executeQuery();
			List<ThinEntry> batchLedger = new ArrayList<ThinEntry>();
			while (rs.next()) {
				ThinEntry thinEntry = new ThinEntry();
				thinEntry.setEntryID(rs.getLong("id"));
				thinEntry.setActive(rs.getBoolean("isactive"));
				thinEntry.setPatientName(rs.getString("patient_name"));
				thinEntry.setAccountNumber(rs.getString("accountnumber"));
				String billableName = rs.getString("procedure_name");
				if (billableName == null)
					billableName = rs.getString("supply_resource_name");
				thinEntry.setBillableName(billableName);
				thinEntry.setTransactionDate(rs.getDate("transactiondate"));
				thinEntry.setTransactionKind(TransactionKind.values()[rs.getInt("transactionkind")]);
				thinEntry.setAmount(rs.getBigDecimal("amount"));
				thinEntry.setTransactionType(TransactionType.values()[rs.getInt("transactiontype")]);
				thinEntry.setJournalCode(rs.getString("journal_code"));
				thinEntry.setProviderName(rs.getString("provider"));
				thinEntry.setAcceptAssignment(rs.getBoolean("acceptassignment"));
				thinEntry.setGenerateClaim(rs.getBoolean("generateclaim"));
				thinEntry.setBilledDate(rs.getDate("billdate"));
				thinEntry.setPatientResponsibility(rs.getBigDecimal("patientresponsibility"));
				thinEntry.setDue(rs.getBigDecimal("due"));
				if (rs.getObject("fromtokind") != null)
					thinEntry.setFromTo(FromToKind.values()[rs.getInt("fromtokind")]);
				batchLedger.add(thinEntry);
			}
			rs.close();
			ps.close();
			result.setThinEntries(batchLedger);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}

	// KKORNEL: AES-10645
	private LedgerResult checkBatch(Batch batch) {
		LedgerResult result = new LedgerResult();
		try {
			if (isBatchClosed(batch)) {
				result.setIsBatchClosed(Boolean.TRUE);
			} else {
				result.setIsBatchClosed(Boolean.FALSE);
			}
		} catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		return result;
	}
	
	// KKORNEL: AES-10645
	public static boolean isBatchClosed(Batch batch) throws Exception {
		Batch tempBatch = null;
		SessionManager sm = SiteManager.Start();
		if (batch != null && batch.getId() != null) {
			Object[] batchDataFromDb = (Object[])sm.execute("SELECT b.id, b.closed, b.description, b.isShared, b.creator FROM Batch b WHERE b.id = :id").setLong("id", batch.getId().longValue()).uniqueResult();
			if (batchDataFromDb != null) {
				tempBatch = RADBeanFactory.getInstance().createBean(Batch.class);
				tempBatch.setId((Long)batchDataFromDb[0]);
				tempBatch.setClosed((Date)batchDataFromDb[1]);
				tempBatch.setDescription((String)batchDataFromDb[2]);
				tempBatch.setIsShared((Boolean)batchDataFromDb[3]);
				tempBatch.setCreator((Passport)batchDataFromDb[4]);
				tempBatch.setPeriod(batch.getPeriod());
			}
		}
		return !BatchUtils.canPostIntoBatch(tempBatch, getRuntimePassport(sm));
	}
	
	private AccountBgSpecificProperties getBusinessGroupSpecificProperties(Account account, BusinessGroup businessGroup) throws Exception {
		AccountBgSpecificProperties props = null;
		try {
			props = AccountUtils.getBusinessGroupSpecificProperties(account, businessGroup);
		}
		catch (BusinessGroupNotFoundException ex) {
			// create new instance
			props = RADBeanFactory.getInstance().createBean(AccountBgSpecificProperties.class);
			props.setAccount(account);
			props.setBusinessGroup(businessGroup);
		}
		return props;
	}
	
	protected LedgerResult initAccountMaintenance(LedgerJob job, Department moduleDepartment) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			// account
			CustomAccountMaintenanceUI ui = new CustomAccountMaintenanceUI();
			ui.setAccount(sm.get(Account.class, job.getAccount().getId()));
			
			BusinessGroup businessGroup = sm.get(BusinessGroup.class, moduleDepartment.getParent().getId());
			
			ui.setAccountBgSpecificProperties(getBusinessGroupSpecificProperties(ui.getAccount(), businessGroup));			
			if (ui.getAccountBgSpecificProperties().getStatementMessage() != null)
				ui.getAccountBgSpecificProperties().getStatementMessage().getName();
			
//			if (ui.getAccountBgSpecificProperties().getCollector() != null) {
//				ui.getAccountBgSpecificProperties().getCollector().getDisplayString();
//			}
			if (ui.getAccountBgSpecificProperties().getRcmItemOption() != null) {
				RCMItemOption io = ui.getAccountBgSpecificProperties().getRcmItemOption();
				io.getRcmCollector();
				if(io.getRcmCollector() != null) {
					io.getRcmCollector().getDisplayString();
				}
				io.getRcmFUOption().getInitialFUD();
				io.getRcmFUOption().getTransferFUD();
				io.getRcmFUOption().getUseIFUD();
				io.getRcmFUOption().getUseTFUD();
			}
			else { // search for a suitable Collector
				// search for the oldest, unpaid charge with RCM Collector 
				Charge charge = (Charge)sm.execute("FROM Charge c " +
						"WHERE c.account=:account " +
						"AND c.due > 0 " +
						"AND c.batch.period.baseOrganization=:bo " +
						"AND c.rcmOption IS NOT NULL " +
						"AND c.rcmOption.rcmCollector IS NOT NULL " +
						"AND c.rcmOption.rcmCollector.passport IS NOT NULL " +
						"ORDER BY c.transactionDate ASC, entryDate ASC")
						.setParameter("account", ui.getAccount())
						.setParameter("bo", moduleDepartment)
						.setMaxResults(1)
						.uniqueResult();
				if (charge == null) {
					// search for the last charge with RCM Collector
					charge = (Charge)sm.execute("FROM Charge c " +
							"WHERE c.account=:account " +
							"AND c.batch.period.baseOrganization=:bo " +
							"AND c.rcmOption IS NOT NULL " +
							"AND c.rcmOption.rcmCollector IS NOT NULL " +
							"AND c.rcmOption.rcmCollector.passport IS NOT NULL " +
							"ORDER BY c.transactionDate DESC, entryDate DESC")
							.setParameter("account", ui.getAccount())
							.setParameter("bo", moduleDepartment)
							.setMaxResults(1)
							.uniqueResult();
				}
				if (charge != null) {
					ui.setChargeIdentifier(String.format("%s (assigned to #%d)", charge.getRcmOption().getRcmCollector().getDisplayString(), charge.getId()));
				}
			}
			
			//patient
			List<Patient> patients = new ArrayList<Patient>();
			patients.addAll(ui.getAccount().getPatients());
			if (patients.size() > 0) {
				ui.setPatient(patients.get(0));
				ui.getPatient().getPerson().getDisplayName();
			}
			else
				throw new LedgerException(TITLE,"Patient not found!",String.format("Patient not found for Account #%s", ui.getAccount().getAccountNumber()),null);
			
			// last statement billing date
			PatientStatement patientStatement = (PatientStatement)sm.execute("FROM PatientStatement ps " +
					"WHERE ps.account=:account " +
					"AND ps.billStatusWrapper.id=:billed " +
					"ORDER BY ps.created DESC")
					.setParameter("account", ui.getAccount())
					.setLong("billed", BillStatusKind.BILLED.ordinal())
					.setMaxResults(1).uniqueResult();
			if (patientStatement != null)
				ui.setLastStatementBillingDate(patientStatement.getCreated());
			
			// last seen date
			AbstractAppointment aa = (AbstractAppointment)sm.execute("FROM AbstractAppointment aa WHERE aa.person=:person ORDER BY beginTime DESC").setParameter("person", ui.getPatient().getPerson()).setMaxResults(1).uniqueResult();
			if (aa != null)
				ui.setLastSeenDate(new Date(aa.getBeginTime()));
			
			// aging
			Aging aging = AgingCalculatorManager.calculate(sm, moduleDepartment, ui.getAccount(), false);
			ui.setAging(aging);
			result.setAccountMaintenanceUI(ui);
			
			ui.setOrganizationSpecificProperties(PatientUtils.getOrganizationSpecificProperties(ui.getPatient(), moduleDepartment));
			if (ui.getOrganizationSpecificProperties().getClassification() != null)
				ui.getOrganizationSpecificProperties().getClassification().getName();
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult saveAccountMaintenance(LedgerJob job, Department modeuleDepartment) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			Account account = job.getAccountMaintenanceUI().getAccount();
			AccountBgSpecificProperties props = job.getAccountMaintenanceUI().getAccountBgSpecificProperties();
			RCMItemOption io = props.getRcmItemOption();
			if(io != null) {
				NewRCMConductor cnd = new NewRCMConductor(sm, modeuleDepartment);
				cnd.saveItemOption(io);
			}
			sm.openTransaction();
//			RCMCollector collector = props.getCollector();
//			if (collector != null) {
//				if (props.getCollector().getChangedKind() == ChangedKind.DELETED)
//					props.setCollector(null);
//				sm.saveRAD(collector);
//			}
			sm.saveRAD(account);
			sm.saveRAD(props);
			sm.saveRAD(job.getAccountMaintenanceUI().getOrganizationSpecificProperties());
			sm.commitTransaction();
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}

	private LedgerResult initAllocation(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			result.setUnassignedPayments(getActiveOpenUnassignedPaymentsByPatientAndCase(sm, job.getPatientID(), job.getPmCaseID(), job.getModuleDepartment()));
		}
		finally {
			sm.closeSession();
		}
		return result;
	}

	public static List<UnassignedPayment> getActiveOpenUnassignedPaymentsByPatientAndCase(SessionManager sm, long patientID, Long pmCaseID, Department department) throws Exception {

		StringBuilder queryString = new StringBuilder("FROM UnassignedPayment up " +
			"LEFT JOIN FETCH up.journalCode " +
			"LEFT JOIN FETCH up.facility " +
			"LEFT JOIN FETCH up.fromEntry ft " +
			"LEFT JOIN FETCH up.pmCase " +
			"LEFT JOIN FETCH up.billingGroup " +
			"LEFT JOIN FETCH up.lastEditor " +
			"LEFT JOIN FETCH up.provider " +
			"LEFT JOIN FETCH up.provider.person " +
			"WHERE up.isActive=1 AND up.due < 0");
		
		if (pmCaseID != null)
			queryString.append("AND up.pmCase.id=:pmCaseID ");
			
		queryString.append("AND up.patient.id=:patientid " +
			"AND up.batch.period.baseOrganization=:bo");
		
		Query query = sm.execute(queryString.toString())
				.setParameter("patientid", patientID)
				.setParameter("bo", department);
		
		if (pmCaseID != null)
			query.setParameter("pmCaseID", pmCaseID);
		
		List<UnassignedPayment> ups = query.list();
		
		for (UnassignedPayment up : ups) {
			ServerLedgerEntryLoadManager.getChildEntries(up);
			FromToManager.loadFromTo(up.getFromEntry());
		}
		return ups;
	}
	
	private void loadCharge(Charge charge) throws Exception {
		if (charge != null) {
			ServerLedgerEntryLoadManager.getChildEntries(charge);
			charge.getBillCharges().size();
			//charge.getProcedureLogs().size();
			//FIXME : 3.6 Changes
			/*
			charge.getChargeCdmLogAssociations().size();
			*/
			charge.getChargeDiagnosisAssociations().size();
			charge.getRootEntries().size();
			if (charge.getAbstractAppointment() != null) {
				if (charge.getAbstractAppointment().getStatus() != null)
					charge.getAbstractAppointment().getStatus().name();
			}
			FromToManager.loadFromTo(charge.getFromEntry());
			ProcedureUtils.loadProcedure(charge.getProcedure());
			SupplyResourceItemManager.loadSupplyResourceItem(charge.getSupplyResource());
		}
	}

	private LedgerResult initChargePosted(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {

			Charge charge = sm.get(Charge.class, job.getEntryID());
			ServerLedgerEntryLoadManager.loadEntry(charge, job.getModuleDepartment(), sm);
			
			if (DepartmentManager.isSHLike(job.getModuleDepartment())) {
			
				TimeAndMaterialConfiguration configuration = TimeAndMaterialServerUtils.getTimeAndMaterialConfiguration(sm, job.getModuleDepartment());
				CdmCaseHistory cdm = PmCaseUtils.getCdmCaseHistory(charge.getPmCase());
				
				if (configuration.isEnableTimeAndMaterialBilling() && cdm != null && cdm.isReadyToBill()) {
					CaseCostingProvider ccp = ServerServiceManager.getInstance().getCaseCostingProviderService().createCaseCostingProvider(sm, job.getModuleDepartment());
					Map<Charge, List<TimeAndMaterialWorksheetRow>> charges = ccp.getChargeMap(cdm, true, true, null);
					double unitsMustBe = 0;
					if (charges != null) {
						List<TimeAndMaterialWorksheetRow> rows = charges.get(charge);
						if (rows != null) {
							for (TimeAndMaterialWorksheetRow row : rows) {
								unitsMustBe += row.getBillableQuantity();
							}
						}
					}
					result.setUnitsMustBeOnChargePosted(unitsMustBe);
				}
			}
			
			TransactionDomain domain = new TransactionDomain();
			domain.setCharge(charge);
			domain.loadProperties();
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult saveUnassignedPaymentAllocation(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			sm.openTransaction();

			List<Entry> entries = new ArrayList<Entry>();
			Passport owner = getRuntimePassport(sm);
			
			Batch batch = sm.get(Batch.class, job.getBatch().getId());
			
			UnassignedPayment unassignedPayment = sm.get(UnassignedPayment.class, job.getUnassignedPayment().getId());
			
			for (AllocatedAmount aa : job.getAllocatedAmounts()) {
				if (aa.getAllocatedAmount() != null && BigDecimal.ZERO.compareTo(aa.getAllocatedAmount()) < 0) {
				
					Allocation allocation = UnassignedPaymentUtils.createAllocation(unassignedPayment, owner);
					allocation.setAmount(aa.getAllocatedAmount());
					allocation.setBatch(batch);
					allocation.setFromEntry(RADBeanFactory.getInstance().createBean(FromTo.class));
					allocation.getFromEntry().setInsurancePolicy(unassignedPayment.getFromEntry().getInsurancePolicy());
					allocation.getFromEntry().setPerson(unassignedPayment.getFromEntry().getPerson());
					allocation.getFromEntry().setKind(unassignedPayment.getFromEntry().getKind());
					allocation.setJournalCode(job.getAllocationJournalCode());
					allocation.setTransactionDate(new Date());
					allocation.setDue(allocation.getAmount());
					allocation.setIsActive(true);
					EntryUtils.setChanged(unassignedPayment);
					
					Charge charge = sm.get(Charge.class, aa.getChargeID());
					Payment payment = AllocationUtils.getPair(allocation);
					payment.setRootEntry(charge);
					payment.setParent(charge);
					payment.setPaymentType(unassignedPayment.getPaymentType());
					payment.setCheckNumber(unassignedPayment.getCheckNumber());
					payment.setFreePaymentText(unassignedPayment.getBankNumber());
					charge.getChildEntries().add(payment);
					charge.getRootEntries().add(payment);
					payment.setJournalCode(job.getPaymentJournalCode());
					EntryUtils.setChanged(charge);
					
					entries.add(allocation);
				}
			}
			
			LedgerManager.saveEntries(sm, entries);
			
			sm.commitTransaction();
			
			result.setUnassignedPayments(getActiveOpenUnassignedPaymentsByPatientAndCase(sm, job.getUnassignedPayment().getPatient().getId(), job.getPmCaseID(), job.getModuleDepartment()));
			
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getChargesByID(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			List<Charge> charges = new ArrayList<Charge>();
			for (long id : job.getIDList()) {
				Charge charge = sm.get(Charge.class, id);
				loadCharge(charge);
				charges.add(charge);
			}
			result.setCharges(charges);
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initAllocationTableByPatient(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			List<Charge> charges = sm.execute("FROM Charge c " +
					"WHERE c.due > 0 " +
					"AND c.isActive=1 " +
					"AND c.patient.id = :patientid " +
					"AND c.pmCase.id = :pmCaseID " +
					"AND c.batch.period.baseOrganization=:bo")
					.setParameter("patientid", job.getPatientID())
					.setParameter("pmCaseID", job.getPmCaseID())
					.setParameter("bo", job.getModuleDepartment())
					.list();
			List<AllocationTableRow> rows = new ArrayList<AllocationTableRow>();
			for (Charge charge : charges) {
				rows.add(AllocationTableRow.create(charge));
			}
			result.setAllocationTableRows(rows);
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initAllocationTableByCharges(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			List<AllocationTableRow> rows = new ArrayList<AllocationTableRow>();
			for (long id : job.getIDList()) {
				Charge charge = sm.get(Charge.class, id);
				if (BigDecimal.ZERO.compareTo(charge.getDue()) < 0)
					rows.add(AllocationTableRow.create(charge));
			}
			result.setAllocationTableRows(rows);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initPatientLedger(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			TransactionDomain domain = new TransactionDomain();
			LedgerFilter filter = new LedgerFilter();
			filter.setShowInactive(false);
			
			if (AESSettings.getServerSettings().getAmkaiOfficeConfig().getRuntime().getCustomization().isRecentTransactionDefault())
				filter.setViewKind(ViewKind.RECENTTRANSACTIONS);
			else
				filter.setViewKind(ViewKind.CHARGESONLY);
			
			filter.setGroupByKind(GroupByKind.CHARGE);
			
			domain.setLedgerFilter(filter);
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult getChargeCdmAssociation(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			Department department = sm.get(Department.class, job.getModuleDepartment().getId());
			TimeAndMaterialConfiguration configuration = TimeAndMaterialServerUtils.getTimeAndMaterialConfiguration(sm, department);
			result.setTimeAndMaterialBillingEnabled(configuration.isEnableTimeAndMaterialBilling());
			if (result.isTimeAndMaterialBillingEnabled()) {
				CaseCostingProvider ccp = ServerServiceManager.getInstance().getCaseCostingProviderService().createCaseCostingProvider(sm, job.getModuleDepartment());
				Charge charge = sm.get(Charge.class, job.getEntryID());
				CdmCaseHistory cdm = PmCaseUtils.getCdmCaseHistory(charge.getPmCase());
				result.setChargeCdmLogAssociation(ChargeUtils.searchCdmAssociation(ccp.getChargeCdmLogAssociations(cdm, true, true, null), charge));
			}
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initCorrectionScreen(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			Correction correction = sm.get(Correction.class, job.getEntryID());
			Department department = sm.get(Department.class, job.getModuleDepartment().getId());
			ServerLedgerEntryLoadManager.loadEntry(correction.getRootEntry(), department, sm);
			TransactionDomain domain = new TransactionDomain();
			domain.setCorrection(correction);
			domain.setTransactionKind(correction.getTransactionKind());
			domain.loadProperties();
			
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initChargeCDMAssociationScreen(LedgerJob job) throws Exception {
		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			CaseCostingProvider ccp = ServerServiceManager.getInstance().getCaseCostingProviderService().createCaseCostingProvider(sm, job.getModuleDepartment());
			Charge charge = sm.get(Charge.class, job.getEntryID());
			CdmCaseHistory cdm = PmCaseUtils.getCdmCaseHistory(charge.getPmCase());
			ChargeCdmLogAssociation assoc = ChargeUtils.searchCdmAssociation(ccp.getChargeCdmLogAssociations(cdm, true, true, null), charge);
			if (assoc != null) {
				for (Long id : assoc.getProcedureLogIds()) {
					ProcedureLog procedureLog = sm.get(ProcedureLog.class, id);
					result.addProcedureLog(procedureLog);
				}
				for (Long id : assoc.getSupplyLogIds()) {
					SupplyLog supplyLog = sm.get(SupplyLog.class, id);
					result.addSupplyLog(supplyLog);
				}
				for (Long id : assoc.getDepartmentLogIds()) {
					DepartmentLog departmentLog = sm.get(DepartmentLog.class, id);
					result.addDepartmentLog(departmentLog);
				}
			}
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initPaymentEditor(LedgerJob job) throws Exception {
		long start = System.currentTimeMillis();
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			Payment payment = sm.get(Payment.class, job.getEntryID());
			ServerLedgerEntryLoadManager.loadEntry(payment, job.getModuleDepartment(), sm);
			TransactionDomain domain = new TransactionDomain();
			domain.setPayment(payment);
			domain.setTransactionKind(payment.getTransactionKind());
			domain.setCharge(sm.get(Charge.class, payment.getRootEntry().getId()));
			
			domain.loadProperties();
			
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
			log.debug(String.format("Payment Editor initialized in %d msec", System.currentTimeMillis()-start));
		}
		return result;
	}

	private LedgerResult getBatchExpectedInformation(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			Batch batch = sm.get(Batch.class, job.getBatchID());
			result.setNumberOfTransactions(batch.getEntries().size());
			for (Expected expected : batch.getExpecteds()) {
				switch (expected.getKind()) {
				case CHARGE:
					result.setExpectedChargeAmount(expected.getAmount());
					result.setExpectedChargeCount(expected.getTransactionCount());
					break;
				case PAYMENT:
					result.setExpectedPaymentAmount(expected.getAmount());
					result.setExpectedPaymentCount(expected.getTransactionCount());
					break;
				case WRITEOFF:
					result.setExpectedWriteOffAmount(expected.getAmount());
					result.setExpectedWriteOffCount(expected.getTransactionCount());
					break;
				}
			}
			
			List<BaseAmount> l = sm.execute("FROM BaseAmount ba " +
					"WHERE ba.isActive=1 " +
					"AND ba.batch=:batch " +
					"AND ba.transactionKindWrapper.id IN (:charge, :payment, :writeoff)")
					.setParameter("batch", batch)
					.setParameter("charge", TransactionKind.CHARGE.ordinal())
					.setParameter("payment", TransactionKind.PAYMENT.ordinal())
					.setParameter("writeoff", TransactionKind.WRITEOFF.ordinal())
					.list();
			result.setTotalChargeAmount(BigDecimal.ZERO);
			result.setTotalPaymentAmount(BigDecimal.ZERO);
			result.setTotalWriteOffAmount(BigDecimal.ZERO);
			for (BaseAmount ba : l) {
				switch (ba.getTransactionKind()) {
				case CHARGE:
					result.setTotalChargeAmount(result.getTotalChargeAmount().add(ba.getAmount()));
					result.setTotalChargeCount(result.getTotalChargeCount()+1);
					break;
				case PAYMENT:
					result.setTotalPaymentAmount(result.getTotalPaymentAmount().add(ba.getAmount()));
					result.setTotalPaymentCount(result.getTotalPaymentCount()+1);
					break;
				case WRITEOFF:
					result.setTotalWriteOffAmount(result.getTotalWriteOffAmount().add(ba.getAmount()));
					result.setTotalWriteOffCount(result.getTotalWriteOffCount()+1);
					break;
				}
			}
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult initUnassignedPaymentEditor(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			TransactionDomain domain = new TransactionDomain();
			domain.setTransactionKind(TransactionKind.UNASSIGNED_PAYMENT);
			if (job.getEntryID() == null) {	// new Unassigned Payment
				UnassignedPayment up = (UnassignedPayment)RADBeanFactory.getInstance().createEntry(TransactionKind.UNASSIGNED_PAYMENT);
				up.setBatch(sm.get(Batch.class, job.getBatchID()));
				Patient patient = sm.get(Patient.class, job.getPatientID());
				if (patient != null) {
					if (patient.getAccount() != null)
						patient.getAccount().getAccountNumber();
				}
				up.setPatient(patient);
				domain.setUnassignedPayment(up);
			}
			else { // edit Unassigned Payment
				UnassignedPayment up = sm.get(UnassignedPayment.class, job.getEntryID());
				domain.setUnassignedPayment(up);
			}
			domain.loadProperties();
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult downloadThinPatient(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			result.setThinPatient(LedgerServerUtils.getPatient(sm, job.getPatientID(), job.getModuleDepartment()));
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult downloadPatientForPatientLedger(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			Patient patient = sm.get(Patient.class, job.getPatientID());
			if (patient != null) {
				LedgerUtils.getPatientInfo(patient, job.getModuleDepartment());
			}
			result.setPatient(patient);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult addBillDate(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			sm.openTransaction();
			InsuranceBill markAsBilled = RADBeanFactory.getInstance().createBean(InsuranceBill.class);
			markAsBilled.setAccount(null);
			markAsBilled.setBaseOrganization(job.getModuleDepartment());
			markAsBilled.setCreator(getRuntimePassport(sm));
			markAsBilled.setCreated(new Date());
			markAsBilled.setEmcKind(null);
			markAsBilled.setFormatKind(null);
			markAsBilled.setBillStatus(BillStatusKind.MARK_AS_BILLED);
			markAsBilled.setPaperKind(null);
			sm.saveRAD(markAsBilled);		
			
			for (Long id : job.getIDList()) {
				
				Charge charge = sm.get(Charge.class, id);
				if (charge.getCurrentBillCharge() == null && charge.isIsActive()) {
					BillCharge billCharge = RADBeanFactory.getInstance().createBean(BillCharge.class);
					billCharge.setBill(markAsBilled);
					billCharge.setCharge(charge);
					billCharge.setChargedToAmount(charge.getDue());
					billCharge.setChargedToKind(charge.getFromEntry().getKind());
					// KKORNEL: AES-9631
					switch (billCharge.getChargedToKind()) {
						case PRIMARY_INSURANCE:
						case SECONDARY_INSURANCE:
						case TERTIARY_INSURANCE:
							billCharge.setChargedToInsuranceClaimOffice(charge.getFromEntry().getInsurancePolicy().getInsuranceClaimOffice());
							break;
						case PRIMARY_GUARANTOR:
						case SECONDARY_GUARANTOR:
							billCharge.setChargedToPerson(charge.getFromEntry().getPerson());
							break;
					}
					billCharge.setSequence(0);
					sm.saveRAD(billCharge);
					
					charge.setCurrentBillCharge(billCharge);
					sm.saveRAD(charge);
				}
			}
			sm.commitTransaction();
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult removeBillDate(LedgerJob job) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			sm.openTransaction();
			InsuranceBill insuranceBill = GroupRebillProvider.createAndSaveInsuranceBillForRebill(sm, getRuntimePassport(sm), job.getModuleDepartment());	
			for (Long id : job.getIDList()) {
				GroupRebillProvider.rebillChargeAndCreateBillCharge(sm, id, insuranceBill);
			}
			sm.commitTransaction();
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private LedgerResult setGenerateClaim(LedgerJob job, boolean generateClaim) throws Exception {
		SessionManager sm = SiteManager.Start();
		LedgerResult result = new LedgerResult();
		try {
			sm.openTransaction();
			for (Long id : job.getIDList()) {
				Charge charge = sm.get(Charge.class, id);
				if (charge.isIsActive() && (charge.getGenerateClaim() == null || charge.getGenerateClaim().booleanValue() != generateClaim)) {
					charge.setGenerateClaim(generateClaim);
					sm.saveRAD(charge);
				}
			}
			sm.commitTransaction();
		}
		catch (Exception ex) {
			sm.rollbackTransaction();
			logError(ex);
			result.setException(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}

	private LedgerResult createCorrection(LedgerJob job) throws Exception {

		LedgerResult result = new LedgerResult();
		SessionManager sm = SiteManager.Start();
		try {
			Amount amount = sm.get(Amount.class, job.getEntryID());
			ServerLedgerEntryLoadManager.loadEntry(amount, job.getModuleDepartment(), sm);
			CorrectionManager cm = CorrectionManager.createCorrection(amount, getRuntimePassport(sm));
			
			TransactionDomain domain = new TransactionDomain();
			domain.setCorrection(cm.getCorrection());
			domain.loadProperties();
			
			result.setTransactionDomain(domain);
		}
		catch (Exception ex) {
			result.setException(ex);
			logError(ex);
		}
		finally {
			sm.closeSession();
		}
		return result;
	}
	
	private void logError(Exception ex) {
		if (!(ex instanceof AOException))
			log.error(TITLE, ex);
	}
}
